
/**
递归查询学校分类
使用方法: select * from pre_school where FIND_IN_SET(catid, querySchool(2));
 */
DROP FUNCTION IF EXISTS querySchool;
CREATE FUNCTION `querySchool` (areaId INT)
	RETURNS VARCHAR(4000)
BEGIN
	DECLARE sTemp VARCHAR(4000);
	DECLARE sTempChd VARCHAR(4000);

	SET sTemp = '$';
	SET sTempChd = cast(areaId as char);

	WHILE sTempChd is not NULL DO
		SET sTemp = CONCAT(sTemp,',',sTempChd);
		SELECT group_concat(catid) INTO sTempChd FROM pre_school where FIND_IN_SET(upid, sTempChd)>0;
	END WHILE;
	return sTemp;
END;

/**
BEGIN;
INSERT INTO `pre_wangka_order` (`uuid`, `sid`, `uid`, `boss_uid`, `real_money`, `sum_money`, `ticket_money`, `pay_dateline`, `customer_name`, `customer_tel`) VALUES
(uuid(), 1, 1, 1, 100, 100, 0, UNIX_TIMESTAMP(NOW()), '刘散散', '13888669885'),
(uuid(), 1, 1, 1, 50, 50, 0, UNIX_TIMESTAMP(NOW()), '刘散散', '13888669885'),
(uuid(), 1, 1, 1, 200, 200, 0, UNIX_TIMESTAMP(NOW()), '刘散散', '13888669885'),
(uuid(), 1, 1, 1, 80, 100, 20, UNIX_TIMESTAMP(NOW()), '刘吧散', '13888669886'),
(uuid(), 1, 1, 1, 300, 300, 0, UNIX_TIMESTAMP(NOW()), '刘散散', '13888669885'),
(uuid(), 1, 1, 1, 150, 150, 0, UNIX_TIMESTAMP(NOW()), '刘散大', '13888669887'),
(uuid(), 1, 1, 1, 120, 150, 30, UNIX_TIMESTAMP(NOW()), '刘散二', '13888669888');
(uuid(), 1, 1, 1, 305, 300, 0, UNIX_TIMESTAMP(NOW()), '刘散散', '13888669885'),
(uuid(), 1, 1, 1, 151, 150, 0, UNIX_TIMESTAMP(NOW()), '刘散大', '13888669887'),
(uuid(), 1, 1, 1, 121, 150, 30, UNIX_TIMESTAMP(NOW()), '刘散二', '13888669888');
COMMIT;
BEGIN;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=1;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=2;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=4;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `delete_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=5;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=6;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `delete_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=7;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=8;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=9;
UPDATE `pre_wangka_order` SET `status_flag` = 1, `dateline` = UNIX_TIMESTAMP(NOW()) WHERE `id`=10;
COMMIT;

 */
DROP PROCEDURE IF EXISTS `settle_proc`;
CREATE PROCEDURE `settle_proc`()
NOT DETERMINISTIC
CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT '商铺日结算存储过程'
	BEGIN
		SET @datetime = UNIX_TIMESTAMP(concat(CURDATE(), ' 16:00:00'));
		SELECT COUNT(`id`) INTO @counts FROM `pre_wangka_order` WHERE ((`dateline` `dateline` <= @datetime) AND (`status_flag` = 1) AND (`delete_flag` = 0));
		IF (@counts > 0) THEN
			START TRANSACTION;
			INSERT INTO `pre_wangka_settle` (`sid`, `amount`, `fee`, `order_list`, `settlement_day`, `settlement_dateline`)
				SELECT `sid`, ROUND(SUM(`money`), 2), ROUND(SUM(`fee`), 2), group_concat(`id`), CURDATE(), UNIX_TIMESTAMP(NOW()) FROM `pre_wangka_order`
				WHERE ((`dateline` <= @datetime) AND (`status_flag` = 1) AND (`delete_flag` = 0));
			UPDATE `pre_wangka_order` SET `status_flag` = 1 WHERE ((`dateline` <= @datetime) AND (`status_flag` = 0) AND (`delete_flag` = 0));
			COMMIT;
		END IF;
		SELECT * FROM `pre_wangka_settle` WHERE ((`settlement_day` = CURDATE()) AND (`state` = 0));
	END;